#coding=utf-8
from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_
import datetime
from datetime import date
from uliweb.contrib.auth import require_login

def __begin__():
    from uliweb import function
    return function('require_login')()

def get_date():
    from uliweb import request
    from uliweb import settings
    
    begin_Date = request.GET.get('begin_date','')
    end_Date = request.GET.get('end_date','')
    if begin_Date =='':
        begin_Date = settings.PARA.begin_date
    else:
         begin_Date = begin_Date[:10]
    if end_Date =='':
        end_Date = str(date.today())
    else:
        end_Date = end_Date[:10]    
    begin_Date = datetime.datetime.strptime(begin_Date, '%Y-%m-%d')
    end_Date = datetime.datetime.strptime(end_Date, '%Y-%m-%d')
    begin_Date = begin_Date.date()
    end_Date = end_Date.date()
    #print 'ffff',begin_Date,end_Date
    return begin_Date,end_Date



#20101203版本月报表按照版本所处阶段维度展示
@expose('/charts/version/month/report/stage')
def charts_version_month_report_stage():
    from uliweb.orm import get_model
#    from uliweb.utils.generic import ListView
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
#    from views import *
    
    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    req_model = get_model('version_month_report_stage')               
    data = []
#    header = []
    print 'tttt',begin_Date,end_Date
    sql = select([req_model.c.month,req_model.c.total_num,req_model.c.version_num_00,
                  req_model.c.version_num_02,req_model.c.version_num_03,req_model.c.version_num_04,
                  req_model.c.version_num_05,
                  req_model.c.version_num_06,req_model.c.version_num_07,req_model.c.version_num_08,
                  req_model.c.version_num_09,req_model.c.version_num_10,req_model.c.version_num_11,
                  req_model.c.version_num_FN],
                 and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                 ).order_by(desc(req_model.c.month_date))
                
    sql_sum = select(["''",func.sum(req_model.c.total_num),
                          func.sum(req_model.c.version_num_00),
                          func.sum(req_model.c.version_num_02),
        				  func.sum(req_model.c.version_num_03),
        				  func.sum(req_model.c.version_num_04),
                          func.sum(req_model.c.version_num_05),
        				  func.sum(req_model.c.version_num_06),
        				  func.sum(req_model.c.version_num_07),
        				  func.sum(req_model.c.version_num_08),
        				  func.sum(req_model.c.version_num_09),
        				  func.sum(req_model.c.version_num_10),
        				  func.sum(req_model.c.version_num_11),
        				  func.sum(req_model.c.version_num_FN)],
                         and_( req_model.c.month_date >= begin_Date,
                         req_model.c.month_date <= end_Date)
                         )
    
    sql_rs = sql.execute()
    
    ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
    line_flag = 0
    for row in sql_rs:
        row = list(row)
        line_flag = 1
        data.append(row)
    sql_sum_rs = sql_sum.execute()
    if line_flag == 1:
        for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    
    #展示        
    view =  SimpleListView(settings.PARA.version_month_stage, data, id='version_month_report_stage')
        
    if 'download' in request.GET:
        return view.download('version_month_report_stage.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result
    
#20101203版本月报表按照开发中心维度展现版本异常情况   
@expose('/charts/version/month/report/kfzx/bbyc')
def charts_version_month_report_kfzx_bbyc():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('version_month_report_kfzx_bbyc')               
      data = []
  #    header = []
      sql = select([req_model.c.month,
                  req_model.c.yc_num_01,
                  req_model.c.yczz_num_01,
                  req_model.c.zt_num_01,
                  req_model.c.yc_num_02,
                  req_model.c.yczz_num_02,
                  req_model.c.zt_num_02,
                  req_model.c.yc_num_03,
                  req_model.c.yczz_num_03,
                  req_model.c.zt_num_03,
                  req_model.c.yc_num_04,
                  req_model.c.yczz_num_04,
                  req_model.c.zt_num_04,
                  req_model.c.yc_num_05,
                  req_model.c.yczz_num_05,
                  req_model.c.zt_num_05,
                  req_model.c.yc_num_06,
                  req_model.c.yczz_num_06,
                  req_model.c.zt_num_06,
                  req_model.c.yc_num_07,
                  req_model.c.yczz_num_07,
                  req_model.c.zt_num_07,
                  req_model.c.yc_num_08,
                  req_model.c.yczz_num_08,
                  req_model.c.zt_num_08,
                  ],
                 and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                 ).order_by(desc(req_model.c.month_date))

      sql_sum = select(["''",
                    func.sum(req_model.c.yc_num_01),
                    func.sum(req_model.c.yczz_num_01),
                    func.sum(req_model.c.zt_num_01),
                    func.sum(req_model.c.yc_num_02),
                    func.sum(req_model.c.yczz_num_02),
                    func.sum(req_model.c.zt_num_02),
                    func.sum(req_model.c.yc_num_03),
                    func.sum(req_model.c.yczz_num_03),
                    func.sum(req_model.c.zt_num_03),
                    func.sum(req_model.c.yc_num_04),
                    func.sum(req_model.c.yczz_num_04),
                    func.sum(req_model.c.zt_num_04),
                    func.sum(req_model.c.yc_num_05),
                    func.sum(req_model.c.yczz_num_05),
                    func.sum(req_model.c.zt_num_05),
                    func.sum(req_model.c.yc_num_06),
                    func.sum(req_model.c.yczz_num_06),
                    func.sum(req_model.c.zt_num_06),
                    func.sum(req_model.c.yc_num_07),
                    func.sum(req_model.c.yczz_num_07),
                    func.sum(req_model.c.zt_num_07),
                    func.sum(req_model.c.yc_num_08),
                    func.sum(req_model.c.yczz_num_08),
                    func.sum(req_model.c.zt_num_08),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                    )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.version_month_report_kfzx_bbyc, data, id='version_month_report_kfzx_bbyc')
        
      if 'download' in request.GET:
        return view.download('version_month_report_kfzx_bbyc.xls', action='download', timeout=10)                
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#20101206版本月报表按照开发中心维度展现版本异常情况 
@expose('/charts/version/month/report/bbycfx')
def charts_version_month_report_bbycfx():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('version_month_report_bbycfx')               
      data = []
  #    header = []
      sql = select([req_model.c.month,
                  func.sum(req_model.c.delay_num_01),
                  func.sum(req_model.c.delay_num_02),
                  func.sum(req_model.c.delay_num_03),
                  func.sum(req_model.c.delay_num_04),
                  func.sum(req_model.c.delay_num_05),
                  func.sum(req_model.c.delay_num_06),
                  func.sum(req_model.c.delay_num_09),
                  ],
                 and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                 ).group_by(req_model.c.month).order_by(desc(req_model.c.month_date))

      sql_sum = select(["''",
                    func.sum(req_model.c.delay_num_01),
                    func.sum(req_model.c.delay_num_02),
                    func.sum(req_model.c.delay_num_03),
                    func.sum(req_model.c.delay_num_04),
                    func.sum(req_model.c.delay_num_05),
                    func.sum(req_model.c.delay_num_06),
                    func.sum(req_model.c.delay_num_09),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                    )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.version_month_report_bbycfx, data, id='version_month_report_bbycfx')
        
      if 'download' in request.GET:
        return view.download('version_month_report_bbycfx.xls', action='download', timeout=10)                
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result
    
#项目任务书月报表按照开发中心维度展示，20101206修改了展示模板
@expose('/charts/assignment/month/report/kfzx')
def charts_assignment_month_report_kfzx():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_kfzx')               
      data = []
  #    header = []
      sql = select([req_model.c.type,
                    req_model.c.month,
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                   ).order_by(desc(req_model.c.month_date),asc(req_model.c.type))
      sql_sum = select(["''",
                       "'a'",
                      func.sum(req_model.c.new_num_01),
                      func.sum(req_model.c.old_num_01),
                      func.sum(req_model.c.new_num_02),
                      func.sum(req_model.c.old_num_02),
                      func.sum(req_model.c.new_num_03),
                      func.sum(req_model.c.old_num_03),
                      func.sum(req_model.c.new_num_04),
                      func.sum(req_model.c.old_num_04),
                      func.sum(req_model.c.new_num_05),
                      func.sum(req_model.c.old_num_05),
                      func.sum(req_model.c.new_num_06),
                      func.sum(req_model.c.old_num_06),
                      func.sum(req_model.c.new_num_07),
                      func.sum(req_model.c.old_num_07),
                      func.sum(req_model.c.new_num_08),
                      func.sum(req_model.c.old_num_08),
                      ],
                      and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                      )        
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         if row[0] == '1':
            row[0] = u'项目'
         elif row[0] == '2':
            row[0] = u'子项目'
         elif row[0] == '3':
            row[0] = u'非项目任务' 
         elif row[0] == '4':
                    row[0] = u'专项采购任务'
        
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[1] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_kfzx, data, id='assignment_month_report_kfzx')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_kfzx' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)                
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#项目任务书季度报表按照开发中心维度展示
@expose('/charts/assignment/quarter/report/kfzx')
def charts_assignment_quarter_report_kfzx():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_quarter_report_kfzx')               
      data = []
  #    header = []
      sql = select([req_model.c.type,
                    req_model.c.quarter,
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                   ).order_by(desc(req_model.c.month_date),asc(req_model.c.type))
      sql_sum = select(["''",
                       "'a'",
                      func.sum(req_model.c.new_num_01),
                      func.sum(req_model.c.old_num_01),
                      func.sum(req_model.c.new_num_02),
                      func.sum(req_model.c.old_num_02),
                      func.sum(req_model.c.new_num_03),
                      func.sum(req_model.c.old_num_03),
                      func.sum(req_model.c.new_num_04),
                      func.sum(req_model.c.old_num_04),
                      func.sum(req_model.c.new_num_05),
                      func.sum(req_model.c.old_num_05),
                      func.sum(req_model.c.new_num_06),
                      func.sum(req_model.c.old_num_06),
                      func.sum(req_model.c.new_num_07),
                      func.sum(req_model.c.old_num_07),
                      func.sum(req_model.c.new_num_08),
                      func.sum(req_model.c.old_num_08),
                      ],
                      and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                      )        
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         if row[0] == '1':
            row[0] = u'项目'
         elif row[0] == '2':
              row[0] = u'子项目'
         elif row[0] == '3':
              row[0] = u'非项目任务'
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[1] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_quarter_report_kfzx, data, id='assignment_quarter_report_kfzx')
        
      if 'download' in request.GET:
        download_name = 'assignment_quarter_report_kfzx' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)                
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result


#项目任务书月报表按照开发中心、项目异常维度展示，20101206修改了展示模板
@expose('/charts/assignment/month/report/kfzx/xmyc')
def charts_assignment_month_report_kfzx_xmyc():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_kfzx_xmyc')               
      data = []
  #    header = []
      sql = select([req_model.c.month,      
                    req_model.c.yc_num_01,  
                    req_model.c.yczz_num_01,
                    req_model.c.zt_num_01,  
                    req_model.c.yc_num_02,  
                    req_model.c.yczz_num_02,
                    req_model.c.zt_num_02,  
                    req_model.c.yc_num_03,  
                    req_model.c.yczz_num_03,
                    req_model.c.zt_num_03,  
                    req_model.c.yc_num_04,  
                    req_model.c.yczz_num_04,
                    req_model.c.zt_num_04,  
                    req_model.c.yc_num_05,  
                    req_model.c.yczz_num_05,
                    req_model.c.zt_num_05,  
                    req_model.c.yc_num_06,  
                    req_model.c.yczz_num_06,
                    req_model.c.zt_num_06,  
                    req_model.c.yc_num_07,  
                    req_model.c.yczz_num_07,
                    req_model.c.zt_num_07,  
                    req_model.c.yc_num_08,  
                    req_model.c.yczz_num_08,
                    req_model.c.zt_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '1')
                   ).order_by(desc(req_model.c.month_date))
                   
      sql_sum = select(["''",
                   func.sum(req_model.c.yc_num_01),  
                   func.sum(req_model.c.yczz_num_01),
                   func.sum(req_model.c.zt_num_01),  
                   func.sum(req_model.c.yc_num_02),  
                   func.sum(req_model.c.yczz_num_02),
                   func.sum(req_model.c.zt_num_02),  
                   func.sum(req_model.c.yc_num_03),  
                   func.sum(req_model.c.yczz_num_03),
                   func.sum(req_model.c.zt_num_03),  
                   func.sum(req_model.c.yc_num_04),  
                   func.sum(req_model.c.yczz_num_04),
                   func.sum(req_model.c.zt_num_04),  
                   func.sum(req_model.c.yc_num_05),  
                   func.sum(req_model.c.yczz_num_05),
                   func.sum(req_model.c.zt_num_05),  
                   func.sum(req_model.c.yc_num_06),  
                   func.sum(req_model.c.yczz_num_06),
                   func.sum(req_model.c.zt_num_06),  
                   func.sum(req_model.c.yc_num_07),  
                   func.sum(req_model.c.yczz_num_07),
                   func.sum(req_model.c.zt_num_07),  
                   func.sum(req_model.c.yc_num_08),  
                   func.sum(req_model.c.yczz_num_08),
                   func.sum(req_model.c.zt_num_08),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '1')
                   )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_kfzx_xmyc, data, id='assignment_month_report_kfzx_xmyc')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_kfzx_xmyc' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#项目任务书月报表按照开发中心、项目异常维度展示，20101206修改了展示模板
@expose('/charts/assignment/month/report/xmycfx')
def charts_assignment_month_report_xmycfx():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_xmycfx')               
      data = []
  #    header = []
      sql = select([req_model.c.month,      
                    func.sum(req_model.c.delay_num_01),
                    func.sum(req_model.c.delay_num_02),
                    func.sum(req_model.c.delay_num_03),
                    func.sum(req_model.c.delay_num_04),
                    func.sum(req_model.c.delay_num_05),
                    func.sum(req_model.c.delay_num_06),
                    func.sum(req_model.c.delay_num_09),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '1')
                   ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                   
      sql_sum = select(["''",
                    func.sum(req_model.c.delay_num_01),
                    func.sum(req_model.c.delay_num_02),
                    func.sum(req_model.c.delay_num_03),
                    func.sum(req_model.c.delay_num_04),
                    func.sum(req_model.c.delay_num_05),
                    func.sum(req_model.c.delay_num_06),
                    func.sum(req_model.c.delay_num_09),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '1')
                   )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_xmycfx, data, id='assignment_month_report_xmycfx')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_xmycfx' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#项目任务书月报表按照开发中心、项目异常维度展示，20101206修改了展示模板
@expose('/charts/assignment/month/report/stage')
def charts_assignment_month_report_stage():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.month,
                                func.sum(req_model.c.total_num),
                                func.sum(req_model.c.ys_num_zc),
                                func.sum(req_model.c.ys_num_yc),
                                func.sum(req_model.c.cg_num_zc),
                                func.sum(req_model.c.cg_num_yc),
                                func.sum(req_model.c.ss_num_zc),
                                func.sum(req_model.c.ss_num_yc),
                                func.sum(req_model.c.tg_num_zc),
                                func.sum(req_model.c.tg_num_yc),
                                func.sum(req_model.c.yans_num_zc),
                                func.sum(req_model.c.yans_num_yc),
                                func.sum(req_model.c.js_num_zc),
                                func.sum(req_model.c.js_num_yc),
                                func.sum(req_model.c.wc_num_zc),
                                ],
                                and_( req_model.c.month_date >= begin_Date,
                                      req_model.c.month_date <= end_Date,
                                      req_model.c.type == '1',
                                      req_model.c.kfzx == user_zx)
                               ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                               
            sql_sum = select(["''",
                                func.sum(req_model.c.total_num),
                                func.sum(req_model.c.ys_num_zc),
                                func.sum(req_model.c.ys_num_yc),
                                func.sum(req_model.c.cg_num_zc),
                                func.sum(req_model.c.cg_num_yc),
                                func.sum(req_model.c.ss_num_zc),
                                func.sum(req_model.c.ss_num_yc),
                                func.sum(req_model.c.tg_num_zc),
                                func.sum(req_model.c.tg_num_yc),
                                func.sum(req_model.c.yans_num_zc),
                                func.sum(req_model.c.yans_num_yc),
                                func.sum(req_model.c.js_num_zc),
                                func.sum(req_model.c.js_num_yc),
                                func.sum(req_model.c.wc_num_zc),
                                ],
                                and_( req_model.c.month_date >= begin_Date,
                                      req_model.c.month_date <= end_Date,
                                      req_model.c.type == '1',
                                      req_model.c.kfzx == user_zx)
                               )
      else:
         sql = select([req_model.c.month,
                     func.sum(req_model.c.total_num),
                     func.sum(req_model.c.ys_num_zc),
                     func.sum(req_model.c.ys_num_yc),
                     func.sum(req_model.c.cg_num_zc),
                     func.sum(req_model.c.cg_num_yc),
                     func.sum(req_model.c.ss_num_zc),
                     func.sum(req_model.c.ss_num_yc),
                     func.sum(req_model.c.tg_num_zc),
                     func.sum(req_model.c.tg_num_yc),
                     func.sum(req_model.c.yans_num_zc),
                     func.sum(req_model.c.yans_num_yc),
                     func.sum(req_model.c.js_num_zc),
                     func.sum(req_model.c.js_num_yc),
                     func.sum(req_model.c.wc_num_zc),
                     ],
                     and_( req_model.c.month_date >= begin_Date,
                           req_model.c.month_date <= end_Date,
                           req_model.c.type == '1')
                     ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                     
         sql_sum = select(["''",
                     func.sum(req_model.c.total_num),
                     func.sum(req_model.c.ys_num_zc),
                     func.sum(req_model.c.ys_num_yc),
                     func.sum(req_model.c.cg_num_zc),
                     func.sum(req_model.c.cg_num_yc),
                     func.sum(req_model.c.ss_num_zc),
                     func.sum(req_model.c.ss_num_yc),
                     func.sum(req_model.c.tg_num_zc),
                     func.sum(req_model.c.tg_num_yc),
                     func.sum(req_model.c.yans_num_zc),
                     func.sum(req_model.c.yans_num_yc),
                     func.sum(req_model.c.js_num_zc),
                     func.sum(req_model.c.js_num_yc),
                     func.sum(req_model.c.wc_num_zc),
                     ],
                     and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '1')
                     )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      print "sql",sql
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_stage, data, id='assignment_month_report_stage')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_stage' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#项目任务书月报表按照开发中心、项目异常维度展示，20101206修改了展示模板
@expose('/charts/assignment/month/report/xmycfx/zxm')
def charts_assignment_month_report_xmycfx_zxm():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_xmycfx')               
      data = []
  #    header = []
      sql = select([req_model.c.month,      
                    func.sum(req_model.c.delay_num_01),
                    func.sum(req_model.c.delay_num_02),
                    func.sum(req_model.c.delay_num_03),
                    func.sum(req_model.c.delay_num_04),
                    func.sum(req_model.c.delay_num_05),
                    func.sum(req_model.c.delay_num_06),
                    func.sum(req_model.c.delay_num_09),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '2')
                   ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                   
      sql_sum = select(["''",
                    func.sum(req_model.c.delay_num_01),
                    func.sum(req_model.c.delay_num_02),
                    func.sum(req_model.c.delay_num_03),
                    func.sum(req_model.c.delay_num_04),
                    func.sum(req_model.c.delay_num_05),
                    func.sum(req_model.c.delay_num_06),
                    func.sum(req_model.c.delay_num_09),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '2')
                   )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_xmycfx, data, id='assignment_month_report_xmycfx_zxm')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_xmycfx_zxm' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#项目任务书月报表按照开发中心、项目异常维度展示，20101206修改了展示模板
@expose('/charts/assignment/month/report/stage/zxm')
def charts_assignment_month_report_stage_zxm():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.month,
                          func.sum(req_model.c.total_num),
                          func.sum(req_model.c.ys_num_zc),
                          func.sum(req_model.c.ys_num_yc),
                          func.sum(req_model.c.cg_num_zc),
                          func.sum(req_model.c.cg_num_yc),
                          func.sum(req_model.c.ss_num_zc),
                          func.sum(req_model.c.ss_num_yc),
                          func.sum(req_model.c.tg_num_zc),
                          func.sum(req_model.c.tg_num_yc),
                          func.sum(req_model.c.yans_num_zc),
                          func.sum(req_model.c.yans_num_yc),
                          func.sum(req_model.c.js_num_zc),
                          func.sum(req_model.c.js_num_yc),
                          func.sum(req_model.c.wc_num_zc),
                          ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.type == '2',
                                req_model.c.kfzx == user_zx)
                         ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                         
            sql_sum = select(["''",
                          func.sum(req_model.c.total_num),
                          func.sum(req_model.c.ys_num_zc),
                          func.sum(req_model.c.ys_num_yc),
                          func.sum(req_model.c.cg_num_zc),
                          func.sum(req_model.c.cg_num_yc),
                          func.sum(req_model.c.ss_num_zc),
                          func.sum(req_model.c.ss_num_yc),
                          func.sum(req_model.c.tg_num_zc),
                          func.sum(req_model.c.tg_num_yc),
                          func.sum(req_model.c.yans_num_zc),
                          func.sum(req_model.c.yans_num_yc),
                          func.sum(req_model.c.js_num_zc),
                          func.sum(req_model.c.js_num_yc),
                          func.sum(req_model.c.wc_num_zc),
                          ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.type == '2',
                                req_model.c.kfzx == user_zx)
                         )
            
      else:        
          sql = select([req_model.c.month,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.type == '2')
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                       
          sql_sum = select(["''",
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '2')
                       )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_stage, data, id='assignment_month_report_stage')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_stage_zxm' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#项目任务书月报表按照项目所处阶段维度展示非项目任务月度报表
@expose('/charts/assignment/month/report/stage/fxm')
def charts_assignment_month_report_stage_fxm():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_stage_fxm')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
         sql = select([req_model.c.month,
                       func.sum(req_model.c.total_num),
                       func.sum(req_model.c.gys_num_zc),
                       func.sum(req_model.c.gys_num_yc),
                       func.sum(req_model.c.ss_num_zc),
                       func.sum(req_model.c.ss_num_yc),
                       func.sum(req_model.c.yans_num_zc),
                       func.sum(req_model.c.yans_num_yc),
                       func.sum(req_model.c.kffk_num_zc),
                       func.sum(req_model.c.kffk_num_yc),
                       func.sum(req_model.c.wc_num_zc),
                       ],
                       and_( req_model.c.month_date >= begin_Date,
                             req_model.c.month_date <= end_Date,
                             req_model.c.kfzx == user_zx)
                      ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                      
         sql_sum = select(["''",
                           func.sum(req_model.c.total_num),
                           func.sum(req_model.c.gys_num_zc),
                           func.sum(req_model.c.gys_num_yc),
                           func.sum(req_model.c.ss_num_zc),
                           func.sum(req_model.c.ss_num_yc),
                           func.sum(req_model.c.yans_num_zc),
                           func.sum(req_model.c.yans_num_yc),
                           func.sum(req_model.c.kffk_num_zc),
                           func.sum(req_model.c.kffk_num_yc),
                           func.sum(req_model.c.wc_num_zc),
                          ],
                       and_( req_model.c.month_date >= begin_Date,
                             req_model.c.month_date <= end_Date,
                             req_model.c.kfzx == user_zx)
                      )
      else:
          sql = select([req_model.c.month,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.gys_num_zc),
                        func.sum(req_model.c.gys_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.kffk_num_zc),
                        func.sum(req_model.c.kffk_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date)
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                       
          sql_sum = select(["''",
                            func.sum(req_model.c.total_num),
                            func.sum(req_model.c.gys_num_zc),
                            func.sum(req_model.c.gys_num_yc),
                            func.sum(req_model.c.ss_num_zc),
                            func.sum(req_model.c.ss_num_yc),
                            func.sum(req_model.c.yans_num_zc),
                            func.sum(req_model.c.yans_num_yc),
                            func.sum(req_model.c.kffk_num_zc),
                            func.sum(req_model.c.kffk_num_yc),
                            func.sum(req_model.c.wc_num_zc),
                           ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date)
                       )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_stage_fxm, data, id='assignment_month_report_stage_fxm')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_stage_fxm' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#准开发需求月报表按照条线维度展示
@expose('/charts/requirement/month/report/tx')
def charts_requirement_month_report_tx():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_month_report_tx')               
      data = []
  #    header = []
      sql = select([req_model.c.month,     
                    req_model.c.total_num, 
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    req_model.c.new_num_09,
                    req_model.c.old_num_09,
                    req_model.c.new_num_10,
                    req_model.c.old_num_10,                    
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                   ).order_by(desc(req_model.c.month_date))
      sql_sum = select(["''",     
                        func.sum(req_model.c.total_num), 
                        func.sum(req_model.c.new_num_01),
                        func.sum(req_model.c.old_num_01),
                        func.sum(req_model.c.new_num_02),
                        func.sum(req_model.c.old_num_02),
                        func.sum(req_model.c.new_num_03),
                        func.sum(req_model.c.old_num_03),
                        func.sum(req_model.c.new_num_04),
                        func.sum(req_model.c.old_num_04),
                        func.sum(req_model.c.new_num_05),
                        func.sum(req_model.c.old_num_05),
                        func.sum(req_model.c.new_num_06),
                        func.sum(req_model.c.old_num_06),
                        func.sum(req_model.c.new_num_07),
                        func.sum(req_model.c.old_num_07),
                        func.sum(req_model.c.new_num_08),
                        func.sum(req_model.c.old_num_08),
                        func.sum(req_model.c.new_num_09),
                        func.sum(req_model.c.old_num_09),
                        func.sum(req_model.c.new_num_10),
                        func.sum(req_model.c.old_num_10),
                      ],
                      and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                      )        
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.requirement_month_report_tx, data, id='requirement_month_report_tx')
        
      if 'download' in request.GET:
        download_name = 'requirement_month_report_tx' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)                
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#季度报表按照条线维度展示
@expose('/charts/requirement/quarter/report/tx')
def charts_requirement_quarter_report_tx():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_quarter_report_tx')               
      data = []
  #    header = []
      sql = select([req_model.c.quarter,     
                    req_model.c.total_num, 
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    req_model.c.new_num_09,
                    req_model.c.old_num_09,
                    req_model.c.new_num_10,
                    req_model.c.old_num_10,                    
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                   ).order_by(desc(req_model.c.month_date))
      sql_sum = select(["''",     
                        func.sum(req_model.c.total_num), 
                        func.sum(req_model.c.new_num_01),
                        func.sum(req_model.c.old_num_01),
                        func.sum(req_model.c.new_num_02),
                        func.sum(req_model.c.old_num_02),
                        func.sum(req_model.c.new_num_03),
                        func.sum(req_model.c.old_num_03),
                        func.sum(req_model.c.new_num_04),
                        func.sum(req_model.c.old_num_04),
                        func.sum(req_model.c.new_num_05),
                        func.sum(req_model.c.old_num_05),
                        func.sum(req_model.c.new_num_06),
                        func.sum(req_model.c.old_num_06),
                        func.sum(req_model.c.new_num_07),
                        func.sum(req_model.c.old_num_07),
                        func.sum(req_model.c.new_num_08),
                        func.sum(req_model.c.old_num_08),
                        func.sum(req_model.c.new_num_09),
                        func.sum(req_model.c.old_num_09),
                        func.sum(req_model.c.new_num_10),
                        func.sum(req_model.c.old_num_10),                        
                      ],
                      and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                      )        
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.requirement_quarter_report_tx, data, id='requirement_quarter_report_tx')
        
      if 'download' in request.GET:
        download_name = 'requirement_quarter_report_tx' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)                
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#月报表按照开发中心维度展示
@expose('/charts/requirement/month/report/kfzx')
def charts_requirement_month_report_kfzx():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_month_report_kfzx')               
      data = []
  #    header = []
      sql = select([req_model.c.month,
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                   ).order_by(desc(req_model.c.month_date))
      sql_sum = select(["''",
                      func.sum(req_model.c.new_num_01),
                      func.sum(req_model.c.old_num_01),
                      func.sum(req_model.c.new_num_02),
                      func.sum(req_model.c.old_num_02),
                      func.sum(req_model.c.new_num_03),
                      func.sum(req_model.c.old_num_03),
                      func.sum(req_model.c.new_num_04),
                      func.sum(req_model.c.old_num_04),
                      func.sum(req_model.c.new_num_05),
                      func.sum(req_model.c.old_num_05),
                      func.sum(req_model.c.new_num_06),
                      func.sum(req_model.c.old_num_06),
                      func.sum(req_model.c.new_num_07),
                      func.sum(req_model.c.old_num_07),
                      func.sum(req_model.c.new_num_08),
                      func.sum(req_model.c.old_num_08),
                      ],
                      and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                      )        
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    #展示        
      view =  SimpleListView(settings.PARA.requirement_month_report_kfzx, data, id='requirement_month_report_kfzx')
            
      if 'download' in request.GET:
          download_name = 'requirement_month_report_kfzx' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
          return view.download(download_name, action='download', timeout=10)                
      else:
          result = view.run()
          result['begin_date'] = begin_Date
          result['end_date'] = end_Date
          return result

#季报表按照开发中心维度展示
@expose('/charts/requirement/quarter/report/kfzx')
def charts_requirement_quarter_report_kfzx():
        from uliweb.orm import get_model
    #    from uliweb.utils.generic import ListView
        from uliweb.utils.generic import SimpleListView
        from uliweb import request
    #    from views import *
        
        pageno = int(request.GET.get('pageno', 0))    
        begin_Date,end_Date = get_date()
        req_model = get_model('requirement_quarter_report_kfzx')               
        data = []
    #    header = []
        sql = select([req_model.c.quarter,
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                    ).order_by(desc(req_model.c.month_date))
        sql_sum = select(["''",
                        func.sum(req_model.c.new_num_01),
                        func.sum(req_model.c.old_num_01),
                        func.sum(req_model.c.new_num_02),
                        func.sum(req_model.c.old_num_02),
                        func.sum(req_model.c.new_num_03),
                        func.sum(req_model.c.old_num_03),
                        func.sum(req_model.c.new_num_04),
                        func.sum(req_model.c.old_num_04),
                        func.sum(req_model.c.new_num_05),
                        func.sum(req_model.c.old_num_05),
                        func.sum(req_model.c.new_num_06),
                        func.sum(req_model.c.old_num_06),
                        func.sum(req_model.c.new_num_07),
                        func.sum(req_model.c.old_num_07),
                        func.sum(req_model.c.new_num_08),
                        func.sum(req_model.c.old_num_08),
                        ],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                        )        
        sql_rs = sql.execute()
        ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
        line_flag = 0
        for row in sql_rs:
            row = list(row)
            line_flag = 1
            data.append(row)
        sql_sum_rs = sql_sum.execute()
        if line_flag == 1:
            for row in sql_sum_rs:
               row = list(row)
               row[0] = u'总计'
               data.append(row)
    #展示        
        view =  SimpleListView(settings.PARA.requirement_quarter_report_kfzx, data, id='requirement_quarter_report_kfzx')
            
        if 'download' in request.GET:
            download_name = 'requirement_quarter_report_kfzx' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
            return view.download(download_name, action='download', timeout=10)                
        else:
            result = view.run()
            result['begin_date'] = begin_Date
            result['end_date'] = end_Date
            return result

#月报表按照系统维度展示
#@expose('/charts/requirement/month/report/system')
#def charts_requirement_month_report_system():
#        from uliweb.orm import get_model
#        from systems.models import *
#    #    from uliweb.utils.generic import ListView
#        from uliweb.utils.generic import SimpleListView
#        from uliweb import request
#    #    from views import *
#        
#        pageno = int(request.GET.get('pageno', 0))    
#        begin_Date,end_Date = get_date()
#        req_model = get_model('requirement_month_report_system')
#        req_sys = get_model('system')               
#        data = {}
#        #data = {}
#        header = []
#        #sql_sys为取当前未关闭系统的系统名称，按照sys_id升序排序
#        sql_sys = select([req_sys.c.name,],and_(req_sys.c.stage != '4').order_by(asc(req_sys.c.sys_id))
#        sql_line = select([func.distinct(req_model.c.month_date),req_model.c.month,],
#                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
#                    )
#
#        sql = select([req_model.c.month_date,
#                    req_model.c.month,
#                    req_model.c.num,
#                    req_sys.c.name,
#                    ],
#                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.system_id == req_sys.c.system)
#                    ).order_by(desc(req_model.c.month_date))
#        sql_sum = select([func.sum(req_model.c.num),
#                        req_sys.c.name,
#                        req_model.c.system_id,
#                        req_model.c.month_date,
#                        ],
#                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.system_id == req_sys.c.system)
#                        ).group_by(req_model.c.system_id,req_model.c.month_date)
#        
#        sql_rs = sql.execute()
#        ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
#        sql_sys_rs = sql_sys.execute()
#        
#        header[0] = u'月份'
#        i = 1
#        for row in sql_sys_rs:
#            name = row
#            header[i++] = name
#        sql_line_rs = sql_line.execute()
#        for row in sql_line_rs:
#            y,z = row
#            data[y] = {}
#            for h in header:
#                if h == u'月份':
#                   data[y][h] = z 
#                else:
#                   data[y][h] = 0
#        data_row[]   
#        #line_flag = 0
#        title_num = 0
#        #header[title_num++] = u'月份’
#        for row in sql_rs:
#            month_date,month,num,name = row
#            data[month_date][name] = num
#            #line_flag = 1
#            #data.append(row)
#        sql_sum_rs = sql_sum.execute()
#        #if line_flag == 1:
#        #    for row in sql_sum_rs:
#        #       row = list(row)
#        #       row[0] = u'总计'
#        #       data.append(row)
#    #展示 
#        data_view = list(sorted(data.values()))       
#        view =  SimpleListView(header, data_view, id='requirement_month_report_system')
#            
#        if 'download' in request.GET:
#            download_name = 'requirement_month_report_system' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
#            return view.download(download_name, action='download', timeout=10)                
#        else:
#            result = view.run()
#            result['begin_date'] = begin_Date
#            result['end_date'] = end_Date
#            return result

#准开发需求月报表按照所处阶段维度展示
@expose('/charts/requirement/month/report/stage')
def charts_requirement_month_report_stage():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_month_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.month, 
                                func.sum(req_model.c.total), 
                                func.sum(req_model.c.new_ky),
                                func.sum(req_model.c.new_ss),
                                func.sum(req_model.c.new_wc),
                                func.sum(req_model.c.new_yc),
                                func.sum(req_model.c.new_zt),
                                func.sum(req_model.c.old_ky),
                                func.sum(req_model.c.old_ss),
                                func.sum(req_model.c.old_wc),
                                func.sum(req_model.c.old_yc),
                                func.sum(req_model.c.old_zt),
                               ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.kfzx == user_zx)
                          ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
            sql_sum = select(["''",
                            func.sum(req_model.c.total), 
                            func.sum(req_model.c.new_ky),
                            func.sum(req_model.c.new_ss),
                            func.sum(req_model.c.new_wc),
                            func.sum(req_model.c.new_yc),
                            func.sum(req_model.c.new_zt),
                            func.sum(req_model.c.old_ky),
                            func.sum(req_model.c.old_ss),
                            func.sum(req_model.c.old_wc),
                            func.sum(req_model.c.old_yc),
                            func.sum(req_model.c.old_zt),
                            ],
                            and_( req_model.c.month_date >= begin_Date,
                                  req_model.c.month_date <= end_Date,
                                  req_model.c.kfzx == user_zx)
                            )        
            
      else:
          sql = select([req_model.c.month, 
                        func.sum(req_model.c.total), 
                        func.sum(req_model.c.new_ky),
                        func.sum(req_model.c.new_ss),
                        func.sum(req_model.c.new_wc),
                        func.sum(req_model.c.new_yc),
                        func.sum(req_model.c.new_zt),
                        func.sum(req_model.c.old_ky),
                        func.sum(req_model.c.old_ss),
                        func.sum(req_model.c.old_wc),
                        func.sum(req_model.c.old_yc),
                        func.sum(req_model.c.old_zt),
                       ],
                  and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                  ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
          sql_sum = select(["''",
                          func.sum(req_model.c.total), 
                          func.sum(req_model.c.new_ky),
                          func.sum(req_model.c.new_ss),
                          func.sum(req_model.c.new_wc),
                          func.sum(req_model.c.new_yc),
                          func.sum(req_model.c.new_zt),
                          func.sum(req_model.c.old_ky),
                          func.sum(req_model.c.old_ss),
                          func.sum(req_model.c.old_wc),
                          func.sum(req_model.c.old_yc),
                          func.sum(req_model.c.old_zt),
                          ],
                         and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                         )        
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    #展示        
      view =  SimpleListView(settings.PARA.requirement_month_report_stage, data, id='requirement_month_report_stage')
            
      if 'download' in request.GET:
          download_name = 'requirement_month_report_stage' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
          return view.download(download_name, action='download', timeout=10)                
      else:
          result = view.run()
          result['begin_date'] = begin_Date
          result['end_date'] = end_Date
          return result

#月报表按照系统维度展示
@expose('/charts/requirement/month/report/system')
def charts_requirement_month_report_system():
      from uliweb.orm import get_model
      #   from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
      #   from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_month_report_system')
      req_sys = get_model('system')               
      data = {}
      header = []
      #sql_sys为取当前未关闭系统的系统名称，按照sys_id升序排序
      #sql_sys = select([req_sys.c.name,req_sys.c.sys_id],and_(req_sys.c.stage != '4')).order_by(asc(req_sys.c.sys_id))
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql_sys = select([func.distinct(req_sys.c.sys_id),req_sys.c.name],
                             and_(req_model.c.month_date >= begin_Date,
                                  req_model.c.month_date <= end_Date,
                                  req_model.c.system_id == req_sys.c.sys_id,
                                  req_model.c.kfzx == user_zx)).order_by(asc(req_sys.c.sys_id))
            
            sql_line = select([func.distinct(req_model.c.month_date),req_model.c.month,],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.kfzx == user_zx)
                        )
            ########################
            sql = select([req_model.c.month_date,
                        req_model.c.month,
                        req_model.c.num,            
                        req_model.c.system_id,
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.kfzx == user_zx)
                        ).order_by(desc(req_model.c.month_date))
            ######################
            sql_sum = select([func.sum(req_model.c.num),
                            req_model.c.system_id,
                            ],
                            and_( req_model.c.month_date >= begin_Date,
                                  req_model.c.month_date <= end_Date,
                                  req_model.c.kfzx == user_zx)
                            ).group_by(req_model.c.system_id)
      else:        
            sql_sys = select([func.distinct(req_sys.c.sys_id),req_sys.c.name],
                            and_(req_model.c.month_date >= begin_Date,
                                    req_model.c.month_date <= end_Date,
                                    req_model.c.system_id == req_sys.c.sys_id)).order_by(asc(req_sys.c.sys_id))
            
            sql_line = select([func.distinct(req_model.c.month_date),req_model.c.month,],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                        )
            ########################
            sql = select([req_model.c.month_date,
                        req_model.c.month,
                        req_model.c.num,            
                        req_model.c.system_id,
                        ],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                        ).order_by(desc(req_model.c.month_date))
            ######################
            sql_sum = select([func.sum(req_model.c.num),
                            req_model.c.system_id,
                            ],
                            and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                            ).group_by(req_model.c.system_id)
            
      
      #初始化header                
      sql_sys_rs = sql_sys.execute()
      #######################################################
      #header = []
      header.append({'name':'month','verbose_name':u'月份', 'width':70})
      #header.append({'name':'total','verbose_name':'合计', 'width':50})
      for row in sql_sys_rs:
          sys_id,name  = row
          i={}
          i['name']=str(sys_id)
          i['verbose_name']=unicode(name)
          i['width']=70
          header.append(i)
      #print  sql_sys       
      #print 'header',header
      #######################################################
      
      
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      
      sql_line_rs = sql_line.execute()
      ###################################
      for row in sql_line_rs:
          #print 'row',row
          y,z = row
          y = str(y)
          #print 'y',y,'z',z
          data[y] = {}
          data[y]['month'] = z
      #    data[y]['total'] = 0
          #print 'data[y]',data
          #print 'header.count()',header
          #print 'len',len(header)
          sql_sys_rs = sql_sys.execute()
          for row in sql_sys_rs:
              sys_id,name  = row
              sys_id = str(sys_id)
              #print 'h',h
              data[y][sys_id] = 0
      ######################################
      #print 'header',header
      #print 'data',data
      #data_row[]   
      line_flag = 0
      title_num = 0
      ##############
      #total = 0
      ##############
      #header[title_num++] = u'月份’
      for row in sql_rs:
          #print row
          line_flag = 1
          month_date,rr,num,sys_id = row
          month_date = str(month_date)
          sys_id = str(sys_id)
          #print 'month_date',month_date
          #print 'num',num,'data[month_date][name]',data[month_date][name]
          data[month_date][sys_id] = num
      #    total += num
          #print data[month_date][name]
          #print data
      
      #print 'data',data
      data_view = list(sorted(data.values()))
      #print 'data_view',data_view
          #line_flag = 1
          #data.append(row)
      #初始化总计字典
      data = {}
      ####################################
      sql_sys_rs = sql_sys.execute()
      for row in sql_sys_rs:
          sys_id,name  = row
          #print 'h',h
          sys_id = str(sys_id)
          data['month'] = u'总计'
          data[sys_id] = 0
      
      
      #print 'zj',zj
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
          for row in sql_sum_rs:
              num,sys_id, = row
              sys_id = str(sys_id)
              data[sys_id] = num
          data_view.append(data)
      #print data_view
      #示 
      #print header
      #print data_view
      
      #print data_view
      #示 
      #data_view = list(sorted(data.values()))       
      view =  SimpleListView(header, data_view, id='requirement_month_report_system')
          
      if 'download' in request.GET:
          download_name = 'requirement_month_report_system' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
          return view.download(download_name, action='download', timeout=10)                
      else:
          result = view.run()
          result['begin_date'] = begin_Date
          result['end_date'] = end_Date
          return result
      

#准开发需求季度报表按照所处阶段维度展示
@expose('/charts/requirement/quarter/report/stage')
def charts_requirement_quarter_report_stage():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_quarter_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.quarter, 
                                func.sum(req_model.c.total), 
                                func.sum(req_model.c.new_ky),
                                func.sum(req_model.c.new_ss),
                                func.sum(req_model.c.new_wc),
                                func.sum(req_model.c.new_yc),
                                func.sum(req_model.c.new_zt),
                                func.sum(req_model.c.old_ky),
                                func.sum(req_model.c.old_ss),
                                func.sum(req_model.c.old_wc),
                                func.sum(req_model.c.old_yc),
                                func.sum(req_model.c.old_zt),
                               ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.kfzx == user_zx)
                          ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
            sql_sum = select(["''",
                            func.sum(req_model.c.total), 
                            func.sum(req_model.c.new_ky),
                            func.sum(req_model.c.new_ss),
                            func.sum(req_model.c.new_wc),
                            func.sum(req_model.c.new_yc),
                            func.sum(req_model.c.new_zt),
                            func.sum(req_model.c.old_ky),
                            func.sum(req_model.c.old_ss),
                            func.sum(req_model.c.old_wc),
                            func.sum(req_model.c.old_yc),
                            func.sum(req_model.c.old_zt),
                            ],
                            and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.kfzx == user_zx)
                            )        
      else:                  
          sql = select([req_model.c.quarter, 
                        func.sum(req_model.c.total), 
                        func.sum(req_model.c.new_ky),
                        func.sum(req_model.c.new_ss),
                        func.sum(req_model.c.new_wc),
                        func.sum(req_model.c.new_yc),
                        func.sum(req_model.c.new_zt),
                        func.sum(req_model.c.old_ky),
                        func.sum(req_model.c.old_ss),
                        func.sum(req_model.c.old_wc),
                        func.sum(req_model.c.old_yc),
                        func.sum(req_model.c.old_zt),
                       ],
                  and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                  ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
          sql_sum = select(["''",
                          func.sum(req_model.c.total), 
                          func.sum(req_model.c.new_ky),
                          func.sum(req_model.c.new_ss),
                          func.sum(req_model.c.new_wc),
                          func.sum(req_model.c.new_yc),
                          func.sum(req_model.c.new_zt),
                          func.sum(req_model.c.old_ky),
                          func.sum(req_model.c.old_ss),
                          func.sum(req_model.c.old_wc),
                          func.sum(req_model.c.old_yc),
                          func.sum(req_model.c.old_zt),
                          ],
                         and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                         )        
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    #展示        
      view =  SimpleListView(settings.PARA.requirement_quarter_report_stage, data, id='requirement_quarter_report_stage')
            
      if 'download' in request.GET:
          download_name = 'requirement_quarter_report_stage' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
          return view.download(download_name, action='download', timeout=10)                
      else:
          result = view.run()
          result['begin_date'] = begin_Date
          result['end_date'] = end_Date
          return result

#项目任务书季报表按照任务阶段正常、异常展现
@expose('/charts/assignment/quarter/report/stage')
def charts_assignment_quarter_report_stage():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_quarter_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.quarter,
                          func.sum(req_model.c.total_num),
                          func.sum(req_model.c.ys_num_zc),
                          func.sum(req_model.c.ys_num_yc),
                          func.sum(req_model.c.cg_num_zc),
                          func.sum(req_model.c.cg_num_yc),
                          func.sum(req_model.c.ss_num_zc),
                          func.sum(req_model.c.ss_num_yc),
                          func.sum(req_model.c.tg_num_zc),
                          func.sum(req_model.c.tg_num_yc),
                          func.sum(req_model.c.yans_num_zc),
                          func.sum(req_model.c.yans_num_yc),
                          func.sum(req_model.c.js_num_zc),
                          func.sum(req_model.c.js_num_yc),
                          func.sum(req_model.c.wc_num_zc),
                          req_model.c.month_date,],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.type == '1',
                                req_model.c.kfzx == user_zx)
                         ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                         
            sql_sum = select(["''",
                          func.sum(req_model.c.total_num),
                          func.sum(req_model.c.ys_num_zc),
                          func.sum(req_model.c.ys_num_yc),
                          func.sum(req_model.c.cg_num_zc),
                          func.sum(req_model.c.cg_num_yc),
                          func.sum(req_model.c.ss_num_zc),
                          func.sum(req_model.c.ss_num_yc),
                          func.sum(req_model.c.tg_num_zc),
                          func.sum(req_model.c.tg_num_yc),
                          func.sum(req_model.c.yans_num_zc),
                          func.sum(req_model.c.yans_num_yc),
                          func.sum(req_model.c.js_num_zc),
                          func.sum(req_model.c.js_num_yc),
                          func.sum(req_model.c.wc_num_zc),
                          ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.type == '1',
                                req_model.c.kfzx == user_zx)
                         )
      else:              
          sql = select([req_model.c.quarter,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.type == '1')
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                       
          sql_sum = select(["''",
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '1')
                       )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_quarter_report_stage, data, id='assignment_quarter_report_stage')
        
      if 'download' in request.GET:
        download_name = 'assignment_quarter_report_stage' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#项目任务书子项目季度报表按照子项目阶段及异常维度展示
@expose('/charts/assignment/quarter/report/stage/zxm')
def charts_assignment_quarter_report_stage_zxm():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_quarter_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
        sql = select([req_model.c.quarter,
                      func.sum(req_model.c.total_num),
                      func.sum(req_model.c.ys_num_zc),
                      func.sum(req_model.c.ys_num_yc),
                      func.sum(req_model.c.cg_num_zc),
                      func.sum(req_model.c.cg_num_yc),
                      func.sum(req_model.c.ss_num_zc),
                      func.sum(req_model.c.ss_num_yc),
                      func.sum(req_model.c.tg_num_zc),
                      func.sum(req_model.c.tg_num_yc),
                      func.sum(req_model.c.yans_num_zc),
                      func.sum(req_model.c.yans_num_yc),
                      func.sum(req_model.c.js_num_zc),
                      func.sum(req_model.c.js_num_yc),
                      func.sum(req_model.c.wc_num_zc),
                      ],
                      and_( req_model.c.month_date >= begin_Date,
                            req_model.c.month_date <= end_Date,
                            req_model.c.type == '2',
                            req_model.c.kfzx == user_zx)
                     ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                     
        sql_sum = select(["''",
                      func.sum(req_model.c.total_num),
                      func.sum(req_model.c.ys_num_zc),
                      func.sum(req_model.c.ys_num_yc),
                      func.sum(req_model.c.cg_num_zc),
                      func.sum(req_model.c.cg_num_yc),
                      func.sum(req_model.c.ss_num_zc),
                      func.sum(req_model.c.ss_num_yc),
                      func.sum(req_model.c.tg_num_zc),
                      func.sum(req_model.c.tg_num_yc),
                      func.sum(req_model.c.yans_num_zc),
                      func.sum(req_model.c.yans_num_yc),
                      func.sum(req_model.c.js_num_zc),
                      func.sum(req_model.c.js_num_yc),
                      func.sum(req_model.c.wc_num_zc),
                      ],
                      and_( req_model.c.month_date >= begin_Date,
                            req_model.c.month_date <= end_Date,
                            req_model.c.type == '2',
                            req_model.c.kfzx == user_zx)
                     )
        
      else:        
          sql = select([req_model.c.quarter,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.type == '2')
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                       
          sql_sum = select(["''",
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '2')
                       )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_quarter_report_stage, data, id='assignment_quarter_report_stage')
        
      if 'download' in request.GET:
        download_name = 'assignment_quarter_report_stage_zxm' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#项目任务书季度报表按照项目所处阶段维度展示非项目任务月度报表
@expose('/charts/assignment/quarter/report/stage/fxm')
def charts_assignment_quarter_report_stage_fxm():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_quarter_report_stage_fxm')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
         sql = select([req_model.c.quarter,
                       func.sum(req_model.c.total_num),
                       func.sum(req_model.c.gys_num_zc),
                       func.sum(req_model.c.gys_num_yc),
                       func.sum(req_model.c.ss_num_zc),
                       func.sum(req_model.c.ss_num_yc),
                       func.sum(req_model.c.yans_num_zc),
                       func.sum(req_model.c.yans_num_yc),
                       func.sum(req_model.c.kffk_num_zc),
                       func.sum(req_model.c.kffk_num_yc),
                       func.sum(req_model.c.wc_num_zc),
                       ],
                       and_( req_model.c.month_date >= begin_Date,
                             req_model.c.month_date <= end_Date,
                             req_model.c.kfzx == user_zx)
                      ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                      
         sql_sum = select(["''",
                           func.sum(req_model.c.total_num),
                           func.sum(req_model.c.gys_num_zc),
                           func.sum(req_model.c.gys_num_yc),
                           func.sum(req_model.c.ss_num_zc),
                           func.sum(req_model.c.ss_num_yc),
                           func.sum(req_model.c.yans_num_zc),
                           func.sum(req_model.c.yans_num_yc),
                           func.sum(req_model.c.kffk_num_zc),
                           func.sum(req_model.c.kffk_num_yc),
                           func.sum(req_model.c.wc_num_zc),
                          ],
                       and_( req_model.c.month_date >= begin_Date,
                             req_model.c.month_date <= end_Date,
                             req_model.c.kfzx == user_zx)
                      )
         
      else:        
          sql = select([req_model.c.quarter,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.gys_num_zc),
                        func.sum(req_model.c.gys_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.kffk_num_zc),
                        func.sum(req_model.c.kffk_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                       
          sql_sum = select(["''",
                            func.sum(req_model.c.total_num),
                            func.sum(req_model.c.gys_num_zc),
                            func.sum(req_model.c.gys_num_yc),
                            func.sum(req_model.c.ss_num_zc),
                            func.sum(req_model.c.ss_num_yc),
                            func.sum(req_model.c.yans_num_zc),
                            func.sum(req_model.c.yans_num_yc),
                            func.sum(req_model.c.kffk_num_zc),
                            func.sum(req_model.c.kffk_num_yc),
                            func.sum(req_model.c.wc_num_zc),
                           ],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                       )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_quarter_report_stage_fxm, data, id='assignment_quarter_report_stage_fxm')
        
      if 'download' in request.GET:
        download_name = 'assignment_quarter_report_stage_fxm' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#版本季度报表按照版本所处阶段维度展示
@expose('/charts/version/quarter/report/stage')
def charts_version_quarter_report_stage():
    from uliweb.orm import get_model
#    from uliweb.utils.generic import ListView
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
#    from views import *
    
    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    req_model = get_model('version_quarter_report_stage')               
    data = []
#    header = []
    
    sql = select([req_model.c.quarter,req_model.c.total_num,req_model.c.version_num_00,
                  req_model.c.version_num_02,req_model.c.version_num_03,req_model.c.version_num_04,
                  req_model.c.version_num_05,
                  req_model.c.version_num_06,req_model.c.version_num_07,req_model.c.version_num_08,
                  req_model.c.version_num_09,req_model.c.version_num_10,req_model.c.version_num_11,
                  req_model.c.version_num_FN],
                 and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                 ).order_by(desc(req_model.c.month_date))
                
    sql_sum = select(["''",func.sum(req_model.c.total_num),
                          func.sum(req_model.c.version_num_00),
                          func.sum(req_model.c.version_num_02),
        				  func.sum(req_model.c.version_num_03),
        				  func.sum(req_model.c.version_num_04),
                          func.sum(req_model.c.version_num_05),
        				  func.sum(req_model.c.version_num_06),
        				  func.sum(req_model.c.version_num_07),
        				  func.sum(req_model.c.version_num_08),
        				  func.sum(req_model.c.version_num_09),
        				  func.sum(req_model.c.version_num_10),
        				  func.sum(req_model.c.version_num_11),
        				  func.sum(req_model.c.version_num_FN)],
                         and_( req_model.c.month_date >= begin_Date,
                         req_model.c.month_date <= end_Date)
                         )
    
    sql_rs = sql.execute()
    
    ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
    line_flag = 0
    for row in sql_rs:
        row = list(row)
        line_flag = 1
        data.append(row)
    sql_sum_rs = sql_sum.execute()
    if line_flag == 1:
        for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    
    #展示        
    view =  SimpleListView(settings.PARA.version_quarter_stage, data, id='version_quarter_report_stage')
        
    if 'download' in request.GET:
        return view.download('version_quarter_report_stage.xls', action='download', timeout=10)                
    else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#季度报表按照系统维度展示
@expose('/charts/requirement/quarter/report/system')
def charts_requirement_quarter_report_system():
      from uliweb.orm import get_model
      #   from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
      #   from views import *
      
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_quarter_report_system')
      req_sys = get_model('system')               
      data = {}
      header = []
      #sql_sys为取当前未关闭系统的系统名称，按照sys_id升序排序
      #sql_sys = select([req_sys.c.name,req_sys.c.sys_id],and_(req_sys.c.stage != '4')).order_by(asc(req_sys.c.sys_id))
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql_sys = select([func.distinct(req_sys.c.sys_id),req_sys.c.name],
                                   and_(req_model.c.month_date >= begin_Date,
                                        req_model.c.month_date <= end_Date,
                                        req_model.c.system_id == req_sys.c.sys_id,
                                        req_model.c.kfzx == user_zx)).order_by(asc(req_sys.c.sys_id))      
            
            sql_line = select([func.distinct(req_model.c.month_date),req_model.c.quarter,],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.kfzx == user_zx)
                        )

            sql = select([req_model.c.month_date,
                        req_model.c.quarter,
                        req_model.c.num,            
                        req_model.c.system_id,
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.kfzx == user_zx)
                        ).order_by(desc(req_model.c.month_date))
            ######################
            sql_sum = select([func.sum(req_model.c.num),
                            req_model.c.system_id,
                            ],
                            and_( req_model.c.month_date >= begin_Date,
                                  req_model.c.month_date <= end_Date,
                                  req_model.c.kfzx == user_zx)
                            ).group_by(req_model.c.system_id)
                        
      else:          
           sql_sys = select([func.distinct(req_sys.c.sys_id),req_sys.c.name],
                                  and_(req_model.c.month_date >= begin_Date,
                                       req_model.c.month_date <= end_Date,
                                       req_model.c.system_id == req_sys.c.sys_id)).order_by(asc(req_sys.c.sys_id))      
           
           sql_line = select([func.distinct(req_model.c.month_date),req_model.c.quarter,],
                       and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                       )
           ########################
           sql = select([req_model.c.month_date,
                       req_model.c.quarter,
                       req_model.c.num,            
                       req_model.c.system_id,
                       ],
                       and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                       ).order_by(desc(req_model.c.month_date))
           ######################
           sql_sum = select([func.sum(req_model.c.num),
                           req_model.c.system_id,
                           ],
                           and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                           ).group_by(req_model.c.system_id)
           
           
      #初始化header                
      sql_sys_rs = sql_sys.execute()
      #######################################################
      #header = []
      header.append({'name':'quarter','verbose_name':u'季度', 'width':70})
      #header.append({'name':'total','verbose_name':'合计', 'width':50})
      for row in sql_sys_rs:
          sys_id,name  = row
          i={}
          i['name']=str(sys_id)
          i['verbose_name']=unicode(name)
          i['width']=70
          header.append(i)
              
      #print 'header',header
      #######################################################
      
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      
      sql_line_rs = sql_line.execute()
      ###################################
      for row in sql_line_rs:
          #print 'row',row
          y,z = row
          y = str(y)
          #print 'y',y
          #print 'y',y,'z',z
          data[y] = {}
          data[y]['quarter'] = z
      #    data[y]['total'] = 0
          #print 'data[y]',data
          #print 'header.count()',header
          #print 'len',len(header)
          sql_sys_rs = sql_sys.execute()
          for row in sql_sys_rs:
              sys_id,name  = row
              #print 'h',h
              data[y][sys_id] = 0
      ######################################
      #print 'header',header
      #print 'data',data
      #data_row[]   
      line_flag = 0
      title_num = 0
      ##############
      #total = 0
      ##############
      #header[title_num++] = u'月份’
      for row in sql_rs:
          #print row
          line_flag = 1
          month,rr,num,sys_id = row
          month_date = str(month)
          #print 'sss',month_date
          #print 'month_date',month_date
          #print 'num',num,'data[month_date][name]',data[month_date][name]
          data[month_date][sys_id] = num
      #    total += num
          #print data[month_date][name]
          #print data
      
      #print 'data',data
      data_view = list(sorted(data.values()))
      #print 'data_view',data_view
          #line_flag = 1
          #data.append(row)
      #初始化总计字典
      data = {}
      ####################################
      sql_sys_rs = sql_sys.execute()
      for row in sql_sys_rs:
          sys_id,name  = row
          #print 'h',h
          data['quarter'] = u'总计'
          data[sys_id] = 0
      
      
      #print 'zj',zj
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
          for row in sql_sum_rs:
              num,sys_id, = row
              data[sys_id] = num
          data_view.append(data)
      #print data_view
      #示 
      #print header
      #print data_view
      
      #print data_view
      #示 
      #data_view = list(sorted(data.values()))       
      view =  SimpleListView(header, data_view, id='requirement_quarter_report_system')
          
      if 'download' in request.GET:
          download_name = 'requirement_quarter_report_system' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
          return view.download(download_name, action='download', timeout=10)                
      else:
          result = view.run()
          result['begin_date'] = begin_Date
          result['end_date'] = end_Date
          return result

#子项目任务书月报表按照开发中心、项目异常维度展示
@expose('/charts/assignment/month/report/kfzx/xmyc/zxm')
def charts_assignment_month_report_kfzx_xmyc_zxm():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_kfzx_xmyc')               
      data = []
  #    header = []
      sql = select([req_model.c.month,      
                    req_model.c.yc_num_01,  
                    req_model.c.yczz_num_01,
                    req_model.c.zt_num_01,  
                    req_model.c.yc_num_02,  
                    req_model.c.yczz_num_02,
                    req_model.c.zt_num_02,  
                    req_model.c.yc_num_03,  
                    req_model.c.yczz_num_03,
                    req_model.c.zt_num_03,  
                    req_model.c.yc_num_04,  
                    req_model.c.yczz_num_04,
                    req_model.c.zt_num_04,  
                    req_model.c.yc_num_05,  
                    req_model.c.yczz_num_05,
                    req_model.c.zt_num_05,  
                    req_model.c.yc_num_06,  
                    req_model.c.yczz_num_06,
                    req_model.c.zt_num_06,  
                    req_model.c.yc_num_07,  
                    req_model.c.yczz_num_07,
                    req_model.c.zt_num_07,  
                    req_model.c.yc_num_08,  
                    req_model.c.yczz_num_08,
                    req_model.c.zt_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '2')
                   ).order_by(desc(req_model.c.month_date))
                   
      sql_sum = select(["''",
                   func.sum(req_model.c.yc_num_01),  
                   func.sum(req_model.c.yczz_num_01),
                   func.sum(req_model.c.zt_num_01),  
                   func.sum(req_model.c.yc_num_02),  
                   func.sum(req_model.c.yczz_num_02),
                   func.sum(req_model.c.zt_num_02),  
                   func.sum(req_model.c.yc_num_03),  
                   func.sum(req_model.c.yczz_num_03),
                   func.sum(req_model.c.zt_num_03),  
                   func.sum(req_model.c.yc_num_04),  
                   func.sum(req_model.c.yczz_num_04),
                   func.sum(req_model.c.zt_num_04),  
                   func.sum(req_model.c.yc_num_05),  
                   func.sum(req_model.c.yczz_num_05),
                   func.sum(req_model.c.zt_num_05),  
                   func.sum(req_model.c.yc_num_06),  
                   func.sum(req_model.c.yczz_num_06),
                   func.sum(req_model.c.zt_num_06),  
                   func.sum(req_model.c.yc_num_07),  
                   func.sum(req_model.c.yczz_num_07),
                   func.sum(req_model.c.zt_num_07),  
                   func.sum(req_model.c.yc_num_08),  
                   func.sum(req_model.c.yczz_num_08),
                   func.sum(req_model.c.zt_num_08),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '2')
                   )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_kfzx_xmyc, data, id='assignment_month_report_kfzx_xmyc_zxm')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_kfzx_xmyc_zxm' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result

#非项目任务书月报表按照开发中心、项目异常维度展示
@expose('/charts/assignment/month/report/kfzx/xmyc/fxm')
def charts_assignment_month_report_kfzx_xmyc_fxm():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_kfzx_xmyc')               
      data = []
  #    header = []
      sql = select([req_model.c.month,      
                    req_model.c.yc_num_01,  
                    req_model.c.yczz_num_01,
                    req_model.c.zt_num_01,  
                    req_model.c.yc_num_02,  
                    req_model.c.yczz_num_02,
                    req_model.c.zt_num_02,  
                    req_model.c.yc_num_03,  
                    req_model.c.yczz_num_03,
                    req_model.c.zt_num_03,  
                    req_model.c.yc_num_04,  
                    req_model.c.yczz_num_04,
                    req_model.c.zt_num_04,  
                    req_model.c.yc_num_05,  
                    req_model.c.yczz_num_05,
                    req_model.c.zt_num_05,  
                    req_model.c.yc_num_06,  
                    req_model.c.yczz_num_06,
                    req_model.c.zt_num_06,  
                    req_model.c.yc_num_07,  
                    req_model.c.yczz_num_07,
                    req_model.c.zt_num_07,  
                    req_model.c.yc_num_08,  
                    req_model.c.yczz_num_08,
                    req_model.c.zt_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '3')
                   ).order_by(desc(req_model.c.month_date))
                   
      sql_sum = select(["''",
                   func.sum(req_model.c.yc_num_01),  
                   func.sum(req_model.c.yczz_num_01),
                   func.sum(req_model.c.zt_num_01),  
                   func.sum(req_model.c.yc_num_02),  
                   func.sum(req_model.c.yczz_num_02),
                   func.sum(req_model.c.zt_num_02),  
                   func.sum(req_model.c.yc_num_03),  
                   func.sum(req_model.c.yczz_num_03),
                   func.sum(req_model.c.zt_num_03),  
                   func.sum(req_model.c.yc_num_04),  
                   func.sum(req_model.c.yczz_num_04),
                   func.sum(req_model.c.zt_num_04),  
                   func.sum(req_model.c.yc_num_05),  
                   func.sum(req_model.c.yczz_num_05),
                   func.sum(req_model.c.zt_num_05),  
                   func.sum(req_model.c.yc_num_06),  
                   func.sum(req_model.c.yczz_num_06),
                   func.sum(req_model.c.zt_num_06),  
                   func.sum(req_model.c.yc_num_07),  
                   func.sum(req_model.c.yczz_num_07),
                   func.sum(req_model.c.zt_num_07),  
                   func.sum(req_model.c.yc_num_08),  
                   func.sum(req_model.c.yczz_num_08),
                   func.sum(req_model.c.zt_num_08),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '3')
                   )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_kfzx_xmyc, data, id='assignment_month_report_kfzx_xmyc_fxm')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_kfzx_xmyc_fxm' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result
    
#非项目任务书月报表按照开发中心、项目异常维度展示
@expose('/charts/assignment/month/report/xmycfx/fxm')
def charts_assignment_month_report_xmycfx_fxm():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_xmycfx')               
      data = []
  #    header = []
      sql = select([req_model.c.month,      
                    func.sum(req_model.c.delay_num_01),
                    func.sum(req_model.c.delay_num_02),
                    func.sum(req_model.c.delay_num_03),
                    func.sum(req_model.c.delay_num_04),
                    func.sum(req_model.c.delay_num_05),
                    func.sum(req_model.c.delay_num_06),
                    func.sum(req_model.c.delay_num_09),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '3')
                   ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                   
      sql_sum = select(["''",
                    func.sum(req_model.c.delay_num_01),
                    func.sum(req_model.c.delay_num_02),
                    func.sum(req_model.c.delay_num_03),
                    func.sum(req_model.c.delay_num_04),
                    func.sum(req_model.c.delay_num_05),
                    func.sum(req_model.c.delay_num_06),
                    func.sum(req_model.c.delay_num_09),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '3')
                   )
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      sql_sum_rs = sql_sum.execute()
      if line_flag == 1:
         for row in sql_sum_rs:
            row = list(row)
            row[0] = u'总计'
            data.append(row)
    
    #展示        
      view =  SimpleListView(settings.PARA.assignment_month_report_xmycfx, data, id='assignment_month_report_xmycfx_fxm')
        
      if 'download' in request.GET:
        download_name = 'assignment_month_report_xmycfx_fxm' + '_' + str(begin_Date) + '_' + str(end_Date) + '.xls'
        return view.download(download_name, action='download', timeout=10)               
      else:
        result = view.run()
        result['begin_date'] = begin_Date
        result['end_date'] = end_Date
        return result
